USE [BMIG_Mensual_POST_CADENA]
GO

/****** Object:  StoredProcedure [dbo].[WASP_M0003_TABLA_BASE]    Script Date: 11/28/2011 12:07:09 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[WASP_M0003_TABLA_BASE]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[WASP_M0003_TABLA_BASE]
GO

USE [BMIG_Mensual_POST_CADENA]
GO

/****** Object:  StoredProcedure [dbo].[WASP_M0003_TABLA_BASE]    Script Date: 11/28/2011 12:07:09 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[WASP_M0003_TABLA_BASE]
(
  @FECHA_INICIO   VARCHAR(8),
  @FECHA_TERMINO  VARCHAR(8)
)
AS

BEGIN 
	SELECT 
		a.aopre,
		a.aopap,
		a.aomda as tipo_moneda,
		a.aocta as cta,
		a.Aosuc as AGCOLOC,
		a.Aooper as NROCREDITO,
		a.AOMOD AS MODULO,
		NULL AS TIP_OPER,
		null as FOLIO,
		MONEDA=
			case
				when(a.Aomda = 999 AND A.Aopap = 2 )then 'UF'
				else'PE'
		end,
		null as FECGRAB,
		convert(varchar(8),a.Aofval,112) as FECHCOLOC,
		a.Aoimp as MTOBRUTO,
		null as MTOLIQ,
		null as IMPTO,
		null as DESG,
		null as PROTECC,
		null as VIDA,
		null as NOTARIA,
		a.aotasa as TASA,
		null as PLAZO,
		null as CUOTA,
		substring(Pfndoc,1,len(Pfndoc)-1) as RUTDEUD,
		(rtrim(c.pfnom1)+' '+rtrim(c.pfnom2)+' '+rtrim(c.pfape1)+' '+rtrim(c.pfape2)) as NOMDEUD,
		convert(varchar(8),c.pffnac,112) as FECHNAC,
		substring(Pfndoc,len(Pfndoc),1) as DVDEUD,	
		null as DESIT,
		SEXO =
			case
				when(c.pfcant='M')then 0
				else 1
		end
	Into	as_prima_comision_temp
	FROM fsd010  a (nolock),
		 fsr008 b (nolock),
		 fsd002 c (nolock), 
		 JT73109 as coloc (nolock),
		 fsd601 as d
	WHERE
		a.Aosbop=0 and
		a.aocta <> 999999999 and 
		a.aooper <> 999999999  and
		a.aocta =b.Ctnro and 
		b.pendoc = c.pfndoc and 
			coloc.JT73109FAP between @FECHA_INICIO and  @FECHA_TERMINO and
			a.Aooper = coloc.JT73109FPA and
			a.Aosuc = coloc.JT73109SUC and
			a.aooper = d.ppoper and 
			a.aosuc = d.ppsuc and 
			a.aocta = d.ppcta and
			d.ppsbop = 0 and
			d.d601co = 'S'  and
		NOT EXISTS(SELECT *
					FROM as_desistidos as anu
					WHERE a.aocta = anu.hcta and
					a.aooper = anu.hoper and
					a.aosuc = anu.hsucur )
	GROUP BY a.aopre, a.aopap, a.aomda, a.aocta, a.Aosuc, 
			a.Aooper,a.AOMOD, a.Aomda, A.Aopap, a.Aofval, 
			a.Aoimp, a.aotasa, c.pfnom1,c.Pfndoc, c.Pfnom2, 
			c.Pfape1, c.Pfape2, c.Pffnac, c.Pfcant

END
GO

